package com.study.manage.dal;

import java.util.Date;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

import com.study.manage.domain.Order;
import com.study.manage.domain.enumm.ItemType;
import com.study.manage.domain.enumm.OrderState;

public interface OrderRepository
        extends PagingAndSortingRepository<Order, Long>, JpaSpecificationExecutor<Order> {

    Order findByItemTypeAndItemIdAndUserId(ItemType itemType, Long itemId, Long userId);

    @Query("select t from Order t, EvaluateGroupDetail egd,User u "
            + "where t.itemId=egd.evaluateId and t.userId=u.id and (u.name like %:name% or :name is null)"
            + "and t.itemType=0 and egd.groupId=:egId and t.state=1")
    Page<Order> findByEgId(@Param("egId") Long egId, @Param("name") String name, Pageable pageable);

    @Query("select t from Order t, EvaluateGroupDetail egd "
            + "where t.itemId=egd.evaluateId and t.itemType=0 and egd.groupId=:egId and t.state=1")
    List<Order> findByEgId(@Param("egId") Long egId);

    @Query("select count(t.id) from Order t where t.itemType=0 and t.state=1 and t.itemId in (:evaluateIds)")
    int countByEvaluateIdIn(@Param("evaluateIds") List<Long> evaluateIds);

    @Query("select count(t.id) from Order t where t.itemType=0 and t.state=1 and t.itemId in (:evaluateIds) and to_days(createdAt)=to_days(:date)")
    int countByEvaluateIdInAndDay(@Param("evaluateIds") List<Long> evaluateIds,
            @Param("date") Date date);

    @Query("select t.createdAt,count(t.id) from Order t  where t.state=1 and t.itemType=0 and t.itemId in (:evaluateIds) "
            + "and to_days(t.createdAt)>=to_days(:beginAt) and to_days(t.createdAt)<=to_days(:endAt) group by to_days(t.createdAt)")
    List<Object[]> countByEvaluateIdInGroupByDay(@Param("beginAt") Date beginAt,
            @Param("endAt") Date endAt, @Param("evaluateIds") List<Long> evaluateIds);


    @Query(nativeQuery = true,
            value = "select t.created_at,sum(t.item_price),'报名收费' as type from sa_order as t  where t.state=1 and t.item_type=0 and t.item_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,count(t.id),'报名成功人数' as type from sa_evaluate_apply t  where t.state=0 and t.verify=1 and t.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,sum(t.item_price),'商品收费' as type from sa_order as t  where t.state=1 and t.item_type=0 "
                    + "and (t.item_id in (select target_id from sa_evaluate_commodity where type=2 and evaluate_id in (:evaluateIds))"
                    + " or (t.item_id in (select evaluate_id from sa_evaluate_group_detail where group_id in "
                    + "(select target_id from sa_evaluate_commodity where type=1 and evaluate_id in (:evaluateIds))))) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,sum(t.item_count),'商品售卖数' as type from sa_order as t  where t.state=1 and t.item_type=0 "
                    + "and (t.item_id in (select target_id from sa_evaluate_commodity where type=2 and evaluate_id in (:evaluateIds))"
                    + " or (t.item_id in (select evaluate_id from sa_evaluate_group_detail where group_id in "
                    + "(select target_id from sa_evaluate_commodity where type=1 and evaluate_id in (:evaluateIds))))) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)")
    List<Object[]> countByEvaluateIdInGroupByDays(@Param("beginAt") Date beginAt,
            @Param("endAt") Date endAt, @Param("evaluateIds") List<Long> evaluateIds);

    @Query(nativeQuery = true,
            value = "select sum(t.item_price) from sa_order as t  where t.state=1 and t.item_type=0 "
                    + "and (t.item_id in (select target_id from sa_evaluate_commodity where type=2 and evaluate_id in (:evaluateIds))"
                    + " or (t.item_id in (select evaluate_id from sa_evaluate_group_detail where group_id in "
                    + "(select target_id from sa_evaluate_commodity where type=1 and evaluate_id in (:evaluateIds))))) "
                    + "and (to_days(t.created_at)=to_days(:date) or :date is null)")
    Float sumOtherPriceByEvaluateIdAndDate(@Param("evaluateIds") List<Long> evaluateIds,
            @Param("date") Date date);

    @Query("select sum(t.itemPrice) from Order t where t.itemType=0 and t.state=1 and t.itemId in (:evaluateIds) and (to_days(createdAt)=to_days(:date) or :date is null)")
    Float sumPriceByEvaluateIdIn(@Param("evaluateIds") List<Long> evaluateIds,
            @Param("date") Date date);

    @Query("select sum(t.itemPrice) from Order t, EvaluateApply ea where t.userId=ea.userId and t.itemId=ea.evaluateId and t.itemType=0 and t.state=1 and t.itemId in (:evaluateIds) "
            + "and (ea.referenceId=:referenceId or :referenceId is null) and (to_days(t.createdAt)=to_days(:date) or :date is null)")
    Float sumPriceByReferenceId(@Param("evaluateIds") List<Long> evaluateIds,
            @Param("date") Date date, @Param("referenceId") Long referenceId);

    @Query("select t from Order t, User u where t.userId=u.id and  t.itemType=0 and t.itemId=:evaluateId and(u.name like %:name% or :name is null) "
            + "and (t.state=:state or :state is null) "
            + "and (to_days(t.createdAt)>=to_days(:beginAt) or :beginAt is null) "
            + "and (to_days(t.createdAt)<=to_days(:endAt) or :endAt is null) ")
    Page<Order> findByEvaluateIdAndDateAndName(@Param("evaluateId") Long evaluateId,
            @Param("name") String name, @Param("state") OrderState state, Pageable pageable,
            @Param("beginAt") Date beginAt, @Param("endAt") Date endAt);

    @Query("select t from Order t where t.itemType=0 and t.itemId=:evaluateId "
            + "and (t.state=:state or :state is null) "
            + "and (to_days(t.createdAt)>=to_days(:beginAt) or :beginAt is null) "
            + "and (to_days(t.createdAt)<=to_days(:endAt) or :endAt is null) ")
    List<Order> findByEvaluateId(@Param("evaluateId") Long evaluateId,
            @Param("state") OrderState state, @Param("beginAt") Date beginAt,
            @Param("endAt") Date endAt);
}
